<?php
header("Content-type: text/html; charset=utf-8"); 
//引入数据库类
include_once '../DB_Oracle.php';
//设置时区
date_default_timezone_set('PRC');



//根据$action调用不同的函数
$action	= $_POST['action'];
if($action == 'QueryInfo') QueryInfo();
if($action == 'RealTime') RealTime();
if($action == 'QueryHistory') QueryHistory();
if($action == 'QueryTerm') QueryTerm();
if($action == 'QuerySelectOption') QuerySelectOption();


/**
  +----------------------------------------------------------
 * 		    查询供选择的条件，所有区、派出所、车辆、警员
  +----------------------------------------------------------
 */

function QuerySelectOption(){
	include_once "../JSON.php";
	$DBsql  = new DB_Oracle();
	$query  = $_POST['query']; //all 查询所有 online 查询在线
	$result = array();
	
	//查询数据库里要查询多长时间之前在线的
	$sql="select * from dep_time";
	$rowTime=$DBsql->fetch_one_array($sql);
	
	//查询所有区
	$sql = 'select a.area_index,a.area_name from dep_area a';
	$ArrArea = $DBsql->fetch_all_array($sql);
	foreach($ArrArea as $areaKey => $area){
		//将所有区的名字以二维数组的形式压入结果数组$result
		$result[$areaKey]['AREA'] = $area['AREA_NAME'];
		//根据区id查询对该区的单位
		$sql = 'select t.department_index,t.department_name from dep_department t where t.area_index = '.$area['AREA_INDEX'];
		$ArrDepartment = $DBsql->fetch_all_array($sql);
		foreach($ArrDepartment as $departmentKey => $department){
			//将单位的名字压入对应的区的数组中
			$result[$areaKey]['INFO'][$departmentKey]['DEPARTMENT'] = $department['DEPARTMENT_NAME'];
			
			//根据单位id查询查询警车
			$sql_for_all = 'select t.license_plate from dep_vehicle t where t.department_index = '.$department['DEPARTMENT_INDEX'].' and t.vehicle_type_index = 1';
			$sql_for_online = 'select d.license_plate
  from dep_vehicle d,
       (select distinct (t.device_id)
          from loc_vehicle_0101 t
         where t.recv_time > sysdate-'.$rowTime['TIME'].'/(24*3600)
           and t.recv_time < sysdate+'.$rowTime['TIME'].'/(24*3600)) t
 where d.device_id = t.device_id
   and d.department_index = '.$department['DEPARTMENT_INDEX'].'
   and d.vehicle_type_index = 1';
			$sql = $query == 'all'?$sql_for_all:$sql_for_online; //判断查询所有还是查询在线

			$ArrCar = $DBsql->fetch_all_array($sql);
			//print_r($ArrCar);
			$newArrCar = array();
			foreach($ArrCar as $carKey => $car){
				array_push($newArrCar, $car['LICENSE_PLATE']);
			}
			$result[$areaKey]['INFO'][$departmentKey]['CAR'] = $newArrCar;
			//print_r($newArrCar);
			//根据单位id查询警用摩托
			$sql_for_all = 'select t.license_plate from dep_vehicle t where t.department_index = '.$department['DEPARTMENT_INDEX'].' and t.vehicle_type_index = 2';
			$sql_for_online = 'select d.license_plate
  from dep_vehicle d,
       (select distinct (t.device_id)
          from loc_vehicle_0101 t
         where t.recv_time > to_date(\''.date('Y-m-d H:i:s',mktime()-30).'\', \'yyyy-mm-dd hh24:mi:ss\')
           and t.recv_time < to_date(\''.date('Y-m-d H:i:s',mktime()+30).'\', \'yyyy-mm-dd hh24:mi:ss\')) t
 where d.device_id = t.device_id
   and d.department_index = '.$department['DEPARTMENT_INDEX'].'
   and d.vehicle_type_index = 2';
								   
			$sql = $query == 'all'?$sql_for_all:$sql_for_online; //判断查询所有还是查询在线
			
			$ArrMoto = $DBsql->fetch_all_array($sql);
			$newArrMoto = array();
			foreach($ArrMoto as $motoKey => $moto){
				array_push($newArrMoto, $moto['LICENSE_PLATE']);
			}
			$result[$areaKey]['INFO'][$departmentKey]['MOTO'] = $newArrMoto;
			
			//根据单位id查询警员
			$sql_for_all = 'select t.personnel_name,t.personnel_sn from dep_personnel t where t.department_index = '.$department['DEPARTMENT_INDEX'];
			$sql_for_online = 'select a.personnel_name, a.personnel_sn
  from dep_personnel a,
       (select distinct (t.device_id)
          from loc_personnel_0101 t
         where t.recv_time > to_date(\''.date('Y-m-d H:i:s',mktime()-30).'\', \'yyyy-mm-dd hh24:mi:ss\')
           and t.recv_time < to_date(\''.date('Y-m-d H:i:s',mktime()+30).'\', \'yyyy-mm-dd hh24:mi:ss\')) b
 where a.device_id = b.device_id
   and a.department_index = '.$department['DEPARTMENT_INDEX'];
			
			$sql = $query == 'all'?$sql_for_all:$sql_for_online; //判断查询所有还是查询在线
			
			$ArrMan = $DBsql->fetch_all_array($sql);
			$newArrMan = array();
			foreach($ArrMan as $manKey => $man){
				array_push($newArrMan, $man['PERSONNEL_SN'].' - '.$man['PERSONNEL_NAME']);
			}
			$result[$areaKey]['INFO'][$departmentKey]['MAN'] = $newArrMan;
		}
	}
	echo JSON ($result);
}


/**
  +----------------------------------------------------------
 * 							实时轨迹
  +----------------------------------------------------------
 */

//实时路线查询车辆或警员信息
function QueryInfo(){
	$DBsql 		= new DB_Oracle();	//实例化数据库
	$QueryWords = $_POST['QueryWords'];
	$byWhat 	= $_POST['byWhat'];

	if($byWhat == 'plate'){	//判断是车牌号还是警号查询不同的数据库
		//根据车牌号查询车辆信息	
		$sql_for_Vehicle = "select t.*,dt.vehicle_type_name,d.department_name from dep_vehicle t,dep_vehicle_type dt,dep_department d where t.license_plate = '$QueryWords' and t.vehicle_type_index=dt.vehicle_type_index and t.department_index = d.department_index";
		$VehicleArr		 = $DBsql->fetch_array($sql_for_Vehicle);
		//返回车辆信息
		if(!is_array($VehicleArr)){
			$resultStr = 'VehicleQueryNone'; //车牌不存在返回VehicleQueryNone
		}else{
			$resultStr	 = 'id:'.$VehicleArr['VEHICLE_INDEX'].'|name:'.$VehicleArr['VEHICLE_NAME'].'|departmentName:'.$VehicleArr['DEPARTMENT_NAME'].'|type:'.$VehicleArr['VEHICLE_TYPE_NAME'].'|device:'.$VehicleArr['DEVICE_ID'].'|plate:'.$VehicleArr['LICENSE_PLATE'].'|locTable:0|sim:'.$VehicleArr['SIM'];
		}
	}else{
		//根据警号查询警员信息
		$sql_for_person = "select p.personnel_index,p.personnel_name,p.personnel_gender,p.department_index,p.personnel_sn,p.device_id,p.sim,d.department_name from dep_personnel p,dep_department d where p.personnel_sn = '$QueryWords' and p.department_index = d.department_index";
		$PersonArr		= $DBsql->fetch_array($sql_for_person);
		//返回警员信息
		if(!is_array($PersonArr)){
			$resultStr = 'PersonQueryNone'; //警员不存在返回PersonQueryNone
		}else{
			//警员信息
			$resultStr = 'id:'.$PersonArr['PERSONNEL_INDEX'].'|name:'.$PersonArr['PERSONNEL_NAME'].'|sn:'.$PersonArr['PERSONNEL_SN'].'|device:'.$PersonArr['DEVICE_ID'].'|gender:'.$PersonArr['PERSONNEL_GENDER'].'|departmentName:'.$PersonArr['DEPARTMENT_NAME'].'|locTable:1|sim:'.$PersonArr['SIM'];	
		}
		
	}
	echo $resultStr;
}

//实时路线坐标查询
function RealTime(){
	$DBsql 		  = new DB_Oracle();	//实例化数据库
	$device		  = $_POST['device'];
	$time		  = $_POST['time'];
	$locTable	  = $_POST['locTable'];
	
	if($locTable == '0'){
		
		$sql_for_loc  = "select t.longitude,t.latitude,t.speed,to_char(t.recv_time,'yyyy-mm-dd hh24:mi:ss') RECV_TIME from loc_vehicle_0101 t where t.device_id = '".$device."' and t.recv_time > sysdate-60/(24*3600) and rownum<=1 order by t.recv_time desc";

		/*$sql_for_loc  = "select t.longitude,t.latitude,t.speed,to_char(t.recv_time,'yyyy-mm-dd hh24:mi:ss') RECV_TIME from loc_vehicle_0101 t where t.device_id = '".$device."' and t.recv_time > to_date('$time','yyyy-mm-dd hh24:mi:ss') order by t.recv_time asc";*/
	}else{
		$sql_for_loc  = "select t.longitude,t.latitude,t.speed,to_char(t.recv_time,'yyyy-mm-dd hh24:mi:ss') RECV_TIME from loc_personnel_0101 t where t.device_id = '".$device."' and t.recv_time > sysdate-60/(24*3600) and rownum<=1 order by t.recv_time desc";
		/*$sql_for_loc  = "select t.longitude,t.latitude,t.speed,to_char(t.recv_time,'yyyy-mm-dd hh24:mi:ss') RECV_TIME from loc_personnel_0101 t where t.device_id = '".$device."' and t.recv_time > to_date('$time','yyyy-mm-dd hh24:mi:ss') and rownum<=1 order by t.recv_time asc";*/
	}
	$LocArr		  = $DBsql->fetch_all_array($sql_for_loc);
	$resultStr	  = '';
	foreach($LocArr as $k=>$v){
		$resultStr .= $v['LONGITUDE'].','.$v['LATITUDE'].','.$v['RECV_TIME'].','.$v['SPEED'].'|';
	}
	echo substr($resultStr,0,strlen($resultStr)-1);
}

/**
  +----------------------------------------------------------
 * 							历史轨迹
  +----------------------------------------------------------
 */

//历史轨迹
function QueryHistory(){
	$DBsql 		= new DB_Oracle();	//实例化数据库
	$QueryWords = $_POST['QueryWords'];
	$byWhat 	= $_POST['byWhat'];
	$startime	= $_POST['startime'];
	$endtime	= $_POST['endtime'];
	
	if($byWhat == 'plate'){	//判断是车牌号还是警号查询不同的数据库
		//根据车牌号查询车辆信息
		$sql_for_Vehicle = "select t.*,dt.vehicle_type_name,d.department_name from dep_vehicle t,dep_vehicle_type dt,dep_department d where t.license_plate = '$QueryWords' and t.vehicle_type_index=dt.vehicle_type_index and t.department_index = d.department_index";	
		$VehicleArr = $DBsql->fetch_array($sql_for_Vehicle);
		
		//返回车辆信息
		if(!is_array($VehicleArr)){
			$resultStr = 'VehicleQueryNone'; //车牌不存在返回VehicleQueryNone
		}else{
			//查询坐标信息
			$sql_for_loc = "select t.longitude,t.latitude from loc_vehicle_0101 t where t.device_id = '".$VehicleArr['DEVICE_ID']."' and t.recv_time > to_date('".$startime."','yyyy-mm-dd hh24:mi:ss') and t.recv_time < to_date('".$endtime."','yyyy-mm-dd hh24:mi:ss')";
			$LocArr = $DBsql->fetch_all_array($sql_for_loc);
			if(count($LocArr) < 3){
				$resultStr = 'LocQueryNone'; //坐标不存在返回LocQueryNone
			}else{ //将所有坐标信息写成字符串
				$LocStr = '';
				foreach($LocArr as $k=>$v){
					$LocStr .= $v['LONGITUDE'].','.$v['LATITUDE'].'#';
				}
				//车辆信息
				$resultStr = 'id:'.$VehicleArr['VEHICLE_INDEX'].'|name:'.$VehicleArr['VEHICLE_NAME'].'|departmentName:'.$VehicleArr['DEPARTMENT_NAME'].'|type:'.$VehicleArr['VEHICLE_TYPE_NAME'].'|device:'.$VehicleArr['DEVICE_ID'].'|plate:'.$VehicleArr['LICENSE_PLATE'].'|sim:'.$VehicleArr['SIM'].'|loc:'.$LocStr;
			}	
			}
	}else{
		//根据警号查询警员信息
		$sql_for_person = "select p.personnel_index,p.personnel_name,p.personnel_gender,p.department_index,p.personnel_sn,p.device_id,p.sim,d.department_name from dep_personnel p,dep_department d where p.personnel_sn = '$QueryWords' and p.department_index = d.department_index";
		$PersonArr = $DBsql->fetch_array($sql_for_person);
		
		//返回警员信息
		if(!is_array($PersonArr)){
			$resultStr = 'PersonQueryNone'; //警员不存在返回PersonQueryNone
		}else{
			//查询坐标信息
			$sql_for_loc = "select t.longitude,t.latitude from loc_personnel_0101 t where t.device_id = '".$PersonArr['DEVICE_ID']."' and t.recv_time > to_date('".$startime."','yyyy-mm-dd hh24:mi:ss') and t.recv_time < to_date('".$endtime."','yyyy-mm-dd hh24:mi:ss')";
			$LocArr = $DBsql->fetch_all_array($sql_for_loc);

			if(count($LocArr) < 3){
				$resultStr = 'LocQueryNone'; //坐标不存在返回LocQueryNone
			}else{ //将所有坐标信息写成字符串
				$LocStr = '';
				foreach($LocArr as $k=>$v){
					$LocStr .= $v['LONGITUDE'].','.$v['LATITUDE'].'#';
				}
				//车辆信息
				$resultStr	 = 'id:'.$PersonArr['PERSONNEL_INDEX'].'|name:'.$PersonArr['PERSONNEL_NAME'].'|sn:'.$PersonArr['PERSONNEL_SN'].'|device:'.$PersonArr['DEVICE_ID'].'|gender:'.$PersonArr['PERSONNEL_GENDER'].'|departmentName:'.$PersonArr['DEPARTMENT_NAME'].'|sim:'.$PersonArr['SIM'].'|loc:'.$LocStr;
			}	
		}
		
	}
	echo $resultStr;
}

/**
  +----------------------------------------------------------
 * 							警力分布
  +----------------------------------------------------------
 */

function QueryTerm(){
	//实例化数据库
	$DBsql = new DB_Oracle();
	//查询所有区
	$sql_for_area = "select * from dep_area";
	$AreaArr	  = $DBsql->fetch_all_array($sql_for_area);

		//查询数据库里要查询多长时间之前在线的
	$sql="select * from dep_time";
	$rowTime=$DBsql->fetch_one_array($sql);

	//遍历所有区
	$ArrStr = '';
	foreach($AreaArr as $area){
		//根据区id查找该区下的所有单位
		$sql_for_department = "select * from dep_department where AREA_INDEX = '".$area['AREA_INDEX']."'";
		$DepartmentArr		= $DBsql->fetch_all_array($sql_for_department);
		$DepartmentStr 		= '';
		foreach($DepartmentArr as $department){
			$DepartmentStr .=  $department['DEPARTMENT_NAME'].',';
		}
		//所有区的字符串
		$ArrStr .= $area['AREA_NAME'].'#'.$DepartmentStr.'#'.$area['CENTERLNG'].'#'.$area['CENTERLAT'].'|';
	}
	//查询所有警力类型
	$sql_for_type = "select * from dep_vehicle_type";
	$TypeArr	  = $DBsql->fetch_all_array($sql_for_type);
	//遍历警力类型
	$TypeStr	  = '';
	foreach($TypeArr as $type){
		$TypeStr .= $type['VEHICLE_TYPE_NAME'].',';
	}
	
	//遍历所有单位在线警员与车辆
	$OnlinePersonnelStr = '';
	$OnlineVehicleStr   = '';
	
	//所有在线人员数组
	$sql_for_person_online = "select *
  from (select t.longitude,
               t.latitude,
               t.recv_time,
               t.speed,
               t.angle,
               t.height,
               p.personnel_name,
               p.personnel_gender,
               p.personnel_sn,
               p.device_id,
               p.sim,
               d.department_name,
               d.misc,
               a.area_name
          from loc_personnel_0101 t, dep_personnel p, dep_department d, dep_area a
         where t.device_id = p.device_id
           and p.department_index = d.department_index
           and t.recv_time >sysdate-".$rowTime['TIME']."/(24*3600)
           and t.recv_time <sysdate+".$rowTime['TIME']."/(24*3600)
           and a.area_index = d.area_index) a,
       (select max(t.recv_time) time, t.device_id
          from loc_personnel_0101 t
         where t.recv_time >sysdate-".$rowTime['TIME']."/(24*3600)
           and t.recv_time <sysdate+".$rowTime['TIME']."/(24*3600)
         group by t.device_id) b
 where a.recv_time = b.time
   and a.device_id = b.device_id";

	$AllPersonnelArr = $DBsql->fetch_all_array($sql_for_person_online);
	if(count($AllPersonnelArr)>0){
		foreach($AllPersonnelArr as $personnel){
			$OnlinePersonnelStr .= $personnel['PERSONNEL_NAME'].','.$personnel['PERSONNEL_GENDER'].','.$personnel['PERSONNEL_SN'].','.$personnel['DEPARTMENT_NAME'].','.$personnel['LONGITUDE'].','.$personnel['LATITUDE'].','.$personnel['AREA_NAME'].','.$personnel['DEVICE_ID'].','.$personnel['SIM'].'@';
		}
	}
	
	//所有在线车辆数组
	 $sql_for_vehicle_online = "select *
  from (select t.longitude,
               t.latitude,
               t.device_id,
               t.speed,
               t.angle,
               t.height,
               t.recv_time,
               v.vehicle_name,
               v.license_plate,
               v.sim,
               vt.vehicle_type_name,
               d.department_name,
               a.area_name
          from loc_vehicle_0101 t,
               dep_vehicle      v,
               dep_vehicle_type vt,
               dep_department   d,
               dep_area         a
         where t.device_id = v.device_id
           and v.vehicle_type_index = vt.vehicle_type_index
           and d.department_index = v.department_index
           and t.recv_time >sysdate-".$rowTime['TIME']."/(24*3600)
           and t.recv_time <sysdate+".$rowTime['TIME']."/(24*3600)
           and a.area_index = d.area_index) a,
       (select max(t.recv_time) time, t.device_id
          from loc_vehicle_0101 t
         where t.recv_time >sysdate-60/(24*3600)
           and t.recv_time <sysdate+60/(24*3600)
         group by t.device_id) b
 where a.recv_time = b.time
   and a.device_id = b.device_id";
	$AllVehicleArr = $DBsql->fetch_all_array($sql_for_vehicle_online);
	if(count($AllVehicleArr)>0){
		foreach($AllVehicleArr as $vehicle){
			$OnlineVehicleStr .= $vehicle['VEHICLE_NAME'].','.$vehicle['VEHICLE_TYPE_NAME'].','.$vehicle['LICENSE_PLATE'].','.$vehicle['DEPARTMENT_NAME'].','.$vehicle['LONGITUDE'].','.$vehicle['LATITUDE'].','.$vehicle['AREA_NAME'].','.$vehicle['DEVICE_ID'].','.$vehicle['SIM'].'@';
		}
	}
	echo  $ArrStr.'*'.$TypeStr.'*'.$OnlinePersonnelStr.'*'.$OnlineVehicleStr;
}

?>